package edu.lsnu.equipment_management_system.mapper.select.paging;

import edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.DeviceVo;
import edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.EquipmentVo;
import edu.lsnu.equipment_management_system.pojo.vo.sql_conditions.PageBeginPageSizeUserNoAndConditionVo;
import edu.lsnu.equipment_management_system.pojo.vo.sql_conditions.UserNoAndConditionVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author mzywucai
 * @Description
 * @date 2018/12/27
 */
public interface SearchEquipmentMapperWithPaging {
    /**
     * @Description 根据条件和职工号进行查询(如果是管理员用户可以不输入职工号查询)
     * @author mzywucai
     * @time 2018-12-27
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.DeviceVo>
     * @params  [pageBeginPageSizeUserNoAndConditionVo]
     */
    @Select("select *\n" +
            "from \n" +
            "(\n" +
            "\tselect \n" +
            "\tJBXX_SB.SBBH 'equipmentNo', \n" +
            "\tJBXX_SB.SBMC 'equipmentName', \n" +
            "\tDM_SBZT.SBZT 'useStatus', \n" +
            "\tYHB.XM 'userName',\n" +
            "\tDM_CFCS.CFCS 'place',\n" +
            "\tJBXX_SB.DJ 'price'\n" +
            "\tfrom\n" +
            "\t\t\t-- 在用设备的查询\n" +
            "\tdbo.funSelectSBSY(#{userNo}, #{condition}) TMP\n" +
            "\tinner join JBXX_SB on JBXX_SB.SBBH=TMP.SBBH \n" +
            "\tinner join \n" +
            "\t(\n" +
            "\t\tselect JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.CFCSDM\n" +
            "\t\tfrom JBXX_SBSY\n" +
            "\t\twhere JBXX_SBSY.SYRQ2 is null\t\n" +
            "\t)JBXX_SBSY_TMP\n" +
            "\ton JBXX_SB.SBBH = JBXX_SBSY_TMP.SBBH\n" +
            "\tinner join DM_SBZT \n" +
            "\ton DM_SBZT.SBZTDM = JBXX_SB.SBZTDM\n" +
            "\tinner join YHB\n" +
            "\ton YHB.ZGH = JBXX_SBSY_TMP.ZGH\n" +
            "\tinner join DM_CFCS\n" +
            "\ton DM_CFCS.CFCSDM = JBXX_SBSY_TMP.CFCSDM\n" +
            "\n" +
            "\tUNION ALL\n" +
            "\n" +
            "\tselect \n" +
            "\tJBXX_SB.SBBH 'equipmentNo', \n" +
            "\tJBXX_SB.SBMC 'equipmentName', \n" +
            "\tDM_SBZT.SBZT 'useStatus', \n" +
            "\tcast('无' as varchar(20)) 'userName',\n" +
            "\tcast('未知' as varchar(20)) 'place',\n" +
            "\tJBXX_SB.DJ 'price'\n" +
            "\tfrom\n" +
            "\t\t\t-- 调出设备的查询\n" +
            "\tdbo.funSelectDRDC(#{userNo}, #{condition}) TMP\n" +
            "\tinner join JBXX_DRDC \n" +
            "\ton JBXX_DRDC.SBBH = TMP.SBBH \n" +
            "\tinner join JBXX_SB\n" +
            "\ton JBXX_SB.SBBH = JBXX_DRDC.SBBH\n" +
            "\tinner join DM_SBZT\n" +
            "\ton DM_SBZT.SBZTDM = JBXX_SB.SBZTDM\n" +
            "\t-- inner join YHB\n" +
            "\t-- on YHB.ZGH = JBXX_DRDC.ZGH\n" +
            "\twhere JBXX_DRDC.DCDR = '调出'\n" +
            "\n" +
            "\tUNION ALL\n" +
            "\n" +
            "\tselect \n" +
            "\tJBXX_SB.SBBH 'equipmentNo', \n" +
            "\tJBXX_SB.SBMC 'equipmentName', \n" +
            "\tDM_SBZT.SBZT 'useStatus', \n" +
            "\tcast('无' as varchar(20)) 'userName',\n" +
            "\tcast('未知' as varchar(20)) 'place',\n" +
            "\tJBXX_SB.DJ 'price'\n" +
            "\tfrom\n" +
            "\t\t\t-- 报损报废设备的查询\n" +
            "\tdbo.funSelectBSBF(#{userNo}, #{condition}) TMP\n" +
            "\tinner join JBXX_BSBF \n" +
            "\ton JBXX_BSBF.SBBH = TMP.SBBH \n" +
            "\tinner join JBXX_SB\n" +
            "\ton JBXX_SB.SBBH = JBXX_BSBF.SBBH\n" +
            "\tinner join DM_SBZT\n" +
            "\ton DM_SBZT.SBZTDM = JBXX_SB.SBZTDM\n" +
            "\tinner join YHB\n" +
            "\ton YHB.ZGH = JBXX_BSBF.ZGH\n" +
            ") RES\n" +
            "order by RES.equipmentNo desc offset #{pageBegin} rows fetch next #{pageSize} rows only")
    List<DeviceVo> searchEquipment(PageBeginPageSizeUserNoAndConditionVo pageBeginPageSizeUserNoAndConditionVo);

    /**
     * @Description 查询上面的的List的count
     * @author mzywucai
     * @time 2018-12-27
     * @see 
     * @return   int
     * @params  [userNoAndConditionVo]
     */
    @Select("select count(RES.equipmentNo)\n" +
            "from \n" +
            "(\n" +
            "\tselect \n" +
            "\tJBXX_SB.SBBH 'equipmentNo'\n" +
            "\tfrom\n" +
            "\t\t\t-- 在用设备的查询\n" +
            "\tdbo.funSelectSBSY(#{userNo}, #{condition}) TMP\n" +
            "\tinner join JBXX_SB on JBXX_SB.SBBH=TMP.SBBH \n" +
            "\tinner join \n" +
            "\t(\n" +
            "\t\tselect JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.CFCSDM\n" +
            "\t\tfrom JBXX_SBSY\n" +
            "\t\twhere JBXX_SBSY.SYRQ2 is not null\t\n" +
            "\t)JBXX_SBSY_TMP\n" +
            "\ton JBXX_SB.SBBH = JBXX_SBSY_TMP.SBBH\n" +
            "\tinner join DM_SBZT \n" +
            "\ton DM_SBZT.SBZTDM = JBXX_SB.SBZTDM\n" +
            "\tinner join YHB\n" +
            "\ton YHB.ZGH = JBXX_SBSY_TMP.ZGH\n" +
            "\tinner join DM_CFCS\n" +
            "\ton DM_CFCS.CFCSDM = JBXX_SBSY_TMP.CFCSDM\n" +
            "\n" +
            "\tUNION ALL\n" +
            "\n" +
            "\tselect \n" +
            "\tJBXX_SB.SBBH 'equipmentNo'\n" +
            "\tfrom\n" +
            "\t\t\t-- 调出设备的查询\n" +
            "\tdbo.funSelectDRDC(#{userNo}, #{condition}) TMP\n" +
            "\tinner join JBXX_DRDC \n" +
            "\ton JBXX_DRDC.SBBH = TMP.SBBH \n" +
            "\tinner join JBXX_SB\n" +
            "\ton JBXX_SB.SBBH = JBXX_DRDC.SBBH\n" +
            "\tinner join DM_SBZT\n" +
            "\ton DM_SBZT.SBZTDM = JBXX_SB.SBZTDM\n" +
            "\t-- inner join YHB\n" +
            "\t-- on YHB.ZGH = JBXX_DRDC.ZGH\n" +
            "\twhere JBXX_DRDC.DCDR = '调出'\n" +
            "\n" +
            "\tUNION ALL\n" +
            "\n" +
            "\tselect \n" +
            "\tJBXX_SB.SBBH 'equipmentNo'\n" +
            "\tfrom\n" +
            "\t\t\t-- 报损报废设备的查询\n" +
            "\tdbo.funSelectBSBF(#{userNo}, #{condition}) TMP\n" +
            "\tinner join JBXX_BSBF \n" +
            "\ton JBXX_BSBF.SBBH = TMP.SBBH \n" +
            "\tinner join JBXX_SB\n" +
            "\ton JBXX_SB.SBBH = JBXX_BSBF.SBBH\n" +
            "\tinner join DM_SBZT\n" +
            "\ton DM_SBZT.SBZTDM = JBXX_SB.SBZTDM\n" +
            "\tinner join YHB\n" +
            "\ton YHB.ZGH = JBXX_BSBF.ZGH\n" +
            ") RES")
    int searchEquipmentCount(UserNoAndConditionVo userNoAndConditionVo);

    /**
     * @Description 查看设备状态
     * @author mzywucai
     * @time 2019-01-04
     * @see 
     * @return   java.lang.String
     * @params  []
     */
    @Select("select JBXX_SB.SBZTDM\n" +
            "from JBXX_SB\n" +
            "where JBXX_SB.SBBH = #{equipmentNo}")
    String equipmentStatus(String equipmentNo);

    /**
     * @Description 查询有使用者的设备
     * @author mzywucai
     * @time 2019-01-05
     * @see 
     * @return   edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.EquipmentVo
     * @params  [equipmentNo]
     */
    @Select("select JBXX_SB.SBBH     'equipmentNo',\n" +
            "       JBXX_SB.SBMC     'equipmentName',\n" +
            "       DM_SBZT.SBZT     'useStatus',\n" +
            "       YHB.XM           'userName',\n" +
            "       DM_CFCS.CFCS     'place',\n" +
            "       JBXX_SB.DJ       'price',\n" +
            "       DM_SBLB.SBLB     'equipmentCategory',\n" +
            "       JBXX_SB.XH       'type',\n" +
            "       JBXX_SB.GG       'guiGe',\n" +
            "       JBXX_SB.ZJZT     'depreciationStatus',\n" +
            "       DM_SBLB.ZJNX     'depreciationPeriod',\n" +
            "       JBXX_SB.CHPHM    'shippingNo',\n" +
            "       JBXX_SB.GHDW     'deliveryUnit',\n" +
            "       JBXX_SB.JHRQ     'purchaseDate',\n" +
            "       JBXX_SB.YT       'application',\n" +
            "       JBXX_SB.CCCJ     'manufacturer',\n" +
            "       JBXX_SB.ZZH      'manufacturerNo',\n" +
            "       JBXX_SB.BZ 'remark'\n" +
            "    ,\n" +
            "       JBXX_SB.FJMC     'attachmentName', -- 新加字段\n" +
            "       JBXX_SB.FJGG     'attachmentGuiGe',\n" +
            "       JBXX_SB.FJSL     'attachmentNumber',\n" +
            "       JBXX_SB.SYDWFZR  'leaderOfUserUnit',\n" +
            "       JBXX_SB.CGR      'purchaser',\n" +
            "       JBXX_SB.YSR      'acceptor',\n" +
            "       JBXX_SB.JZR      'bookkeeper',\n" +
            "       DS.SBLYVBM       'utilizationTemplate'\n" +
            "from JBXX_SB\n" +
            "       left join DM_SBZT\n" +
            "                 on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "       left join\n" +
            "     (\n" +
            "       select JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.CFCSDM, JBXX_SBSY.BZ\n" +
            "       from JBXX_SBSY\n" +
            "       where JBXX_SBSY.SYRQ2 is null\n" +
            "     ) JBXX_SBSY_TMP\n" +
            "     on JBXX_SB.SBBH = JBXX_SBSY_TMP.SBBH\n" +
            "       left join YHB\n" +
            "                 on YHB.ZGH = JBXX_SBSY_TMP.ZGH\n" +
            "       left join DM_CFCS\n" +
            "                 on DM_CFCS.CFCSDM = JBXX_SBSY_TMP.CFCSDM\n" +
            "       left join DM_SBLB\n" +
            "                 on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM\n" +
            "       left join DM_SBLYVBM DS\n" +
            "                 on JBXX_SB.LYVMBDM = DS.SBLYVBMDM\n" +
            "where JBXX_SB.SBBH = #{equipmentNo}")
    EquipmentVo getHasOwnerEquipmentDetail(String equipmentNo);

    /**
     * @Description 查询没有使用者的设备
     * @author mzywucai
     * @time 2019-01-05
     * @see 
     * @return   edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.EquipmentVo
     * @params  [equipmentNo]
     */
    @Select("select JBXX_SB.SBBH              'equipmentNo',\n" +
            "       JBXX_SB.SBMC              'equipmentName',\n" +
            "       DM_SBZT.SBZT              'useStatus',\n" +
            "       cast('无' as varchar(20))  'userName',\n" +
            "       cast('未知' as varchar(20)) 'place',\n" +
            "       JBXX_SB.DJ                'price',\n" +
            "       DM_SBLB.SBLB              'equipmentCategory',\n" +
            "       JBXX_SB.XH                'type',\n" +
            "       JBXX_SB.GG                'guiGe',\n" +
            "       JBXX_SB.ZJZT              'depreciationStatus',\n" +
            "       DM_SBLB.ZJNX              'depreciationPeriod',\n" +
            "       JBXX_SB.CHPHM             'shippingNo',\n" +
            "       JBXX_SB.GHDW              'deliveryUnit',\n" +
            "       JBXX_SB.JHRQ              'purchaseDate',\n" +
            "       JBXX_SB.YT                'application',\n" +
            "       JBXX_SB.CCCJ              'manufacturer',\n" +
            "       JBXX_SB.ZZH               'manufacturerNo',\n" +
            "       JBXX_SB.BZ                'remark'\n" +
            "    ,\n" +
            "       JBXX_SB.FJMC              'attachmentName', -- 新加字段\n" +
            "       JBXX_SB.FJGG              'attachmentGuiGe',\n" +
            "       JBXX_SB.FJSL              'attachmentNumber',\n" +
            "       JBXX_SB.SYDWFZR           'leaderOfUserUnit',\n" +
            "       JBXX_SB.CGR               'purchaser',\n" +
            "       JBXX_SB.YSR               'acceptor',\n" +
            "       JBXX_SB.JZR               'bookkeeper',\n" +
            "       DS.SBLYVBM                'utilizationTemplate'\n" +
            "from JBXX_SB\n" +
            "       left join DM_SBZT\n" +
            "                 on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "       left join DM_SBLB\n" +
            "                 on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM\n" +
            "       left join DM_SBLYVBM DS\n" +
            "                 on JBXX_SB.LYVMBDM = DS.SBLYVBMDM\n" +
            "where JBXX_SB.SBBH = #{equipmentNo}")
    EquipmentVo getOthersEquipmentDetail(String equipmentNo);
}
